import tushare as ts
import pandas as pd
from sqlalchemy import create_engine
from DatetimeUtils import choose_day
import logging
import matplotlib.pyplot as plt
from ListUtils import anti_join
import time

"""
操作shibor利率
"""


def deal_append(query_start_, query_end_, engine, pro):
    """
    通过anti_join函数处理增量数据
    """
    source_list_ = pro.shibor(start_date=query_start_, end_date=query_end_)
    # 最好是单元数组，不然merge之后会有问题。
    sql_ = 'select s.date from shibor s WHERE s.date >= ' + query_start_ + ' and s.date <= ' + query_end_
    target_list_ = pd.read_sql(sql_, engine)
    if len(target_list_) == 0:
        # 数据库内未找到数据则全部添加
        source_list_.to_sql('shibor', con=engine, index=False, index_label='date', if_exists='append')
        print('数据库内未找到数据则全部添加：(%s)' % source_list_)
    else:
        # 数据库内有历史数据则增量添加
        source_list_ = anti_join(source_list_, target_list_, 'date')
        # 采用双for循环性能太差，采用dataFrame合并提高效率
        # source_list['exists'] = False
        # for i in range(0, len(source_list)):
        #     # 查询并插入shior利率数据
        #     that_day = source_list.loc[i].date
        #     for j in range(0, len(target_list)):
        #         # logging.debug('下方if判断：' + trade_cal_days.loc[j].cal_date == that_day and trade_cal_days.loc[j].is_open == '1')
        #         if target_list.loc[j].date == that_day :
        #             # print(source_list.loc[i,['exists']])
        #             source_list.loc[i,['exists']] = True
        #             break

        # source_list = source_list[source_list['exists'] == False]
        if len(source_list_) > 0:
            print('增量：(%s)' % source_list_)
            # print(source_list)
            # source_list.drop(labels='exists',axis=1,inplace=True)
            source_list_.to_sql('shibor', con=engine, index=False, index_label='date', if_exists='append')
    time.sleep(5)


def insert_history_data_shibor(engine, pro):
    """
    保存历史数据，通过对比添加数据
    """
    insert_history_data_flag = True # 是否插入历史数据
    if insert_history_data_flag:

        current_year = choose_day(0).strftime('%Y')
        start_year = 2006 # shibor记录从20061008开始
        """
        处理历史数据
        """
        for i in range(start_year, int(current_year)):
            query_start = '%d' %i + '0101'  # 查询开始
            query_end = '%d' %i  + '1231'  # 查询结束
            deal_append(query_start, query_end, engine, pro)

        """
        处理当年历史数据
        """
        current_day = choose_day(0).strftime('%Y%m%d')
        year_first_day = current_year + '0101'
        deal_append(year_first_day, current_day, engine, pro)



def update_current_data_shibor(engine, pro):
    """
    更新当前数据，后续使用定时器
    """
    insert_current_flag = True
    if insert_current_flag:
        day_updates = 30 #设定更新30天数据
        current_day = choose_day(0).strftime('%Y%m%d')
        that_day = choose_day(day_updates).strftime('%Y%m%d')
        deal_append(that_day, current_day, engine, pro)




def query_shibor_lpr(engine_, query_start_, query_end_):
    """
    获取相关指数日数据
    :param engine_:
    :param query_start_:  起始时间
    :param query_end_:  结束时间
    :return:
    """
    sql_ = 'select s.*, sl.1y  "sl_1y" from shibor s LEFT JOIN shibor_lpr sl on s.`date` = sl.`date` WHERE  s.`date` >= ' + query_start_ + ' and s.`date` <= ' + query_end_ + ' order by s.`date` asc'
    df_ = pd.read_sql(sql_, engine_)
    df_['date'] = pd.to_datetime(df_['date'], format='%Y%m%d', errors='ignore')
    return df_

